<?php
namespace app\services;

use Yii;
use yii\helpers\Url as U;

class ExcelService extends BaseService {
    /**
     * can be used only by  excel service
     */
    public function exportXls($data, $xls, $sheet, $pretty=true) {
        return $this->outXls($data, $xls.'_'.date('Y-m-d_H:i:s'), $sheet, $pretty);
    }

	public function __construct() {		
		/** Error reporting */
		error_reporting(E_ALL);
		ini_set('display_errors', TRUE);
		ini_set('display_startup_errors', TRUE);

		define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

		date_default_timezone_set('Asia/Shanghai');

		if (PHP_SAPI == 'cli')
		die('This example should only be run from a Web Browser');
	}
	
	public function outXls(array $datas, $filename, $title='内容', $pretty=true) {
		//效率优先, 万行数据导出结果为 CSV
		//if (count($datas) > 10000) {
			$this->download_send_headers($filename.".csv");
			echo $this->array2csv($datas);
			die();
		//}

		$cacheMethod = \PHPExcel_CachedObjectStorageFactory::cache_in_memory_serialized;
		$cacheSettings = ['memoryCacheSize' => '500MB'];
		\PHPExcel_Settings::setCacheStorageMethod($cacheMethod, $cacheSettings);
		$objPHPExcel = new \PHPExcel();

		// Set document properties
		$objPHPExcel->getProperties()->setCreator("www.vamaker.com")
									 ->setLastModifiedBy("www.vamaker.com")
									 ->setTitle("Office 2007 XLSX Test Document")
									 ->setSubject("Office 2007 XLSX Test Document")
									 ->setDescription("Vamaker CopyRight Reserved")
									 ->setKeywords("vamaker")
									 ->setCategory("vamaker");
		
		
		// sheet2 原始数据
		$rawSheetIndex = 0;
		if ($pretty) {
			$rawSheetIndex = 1;
			$objPHPExcel->createSheet($rawSheetIndex);
		}
		$objPHPExcel->setActiveSheetIndex($rawSheetIndex);
		$this->renderRawData($objPHPExcel, $datas, $title, $rawSheetIndex);
		
		//若设置格式化
		if ($pretty) {
			// vamaker 报表数据
			$objPHPExcel->setActiveSheetIndex(0);
			$count = count($datas);
			$isFreeze = false;
			if ($count) {
				$this->renderReportData($objPHPExcel, $datas, $title);
				// 设置框架
				$count > 14 && $isFreeze = true;
				$this->setReportBorder($objPHPExcel, $title, $isFreeze);
				// 展示 logo
				$this->setLogo($objPHPExcel);
			}
		}		
		$this->render($filename, $objPHPExcel);
		return true;
	}
	
	private function download_send_headers($filename) {
		// disable caching
		$now = gmdate("D, d M Y H:i:s");
		header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
		header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
		header("Last-Modified: {$now} GMT");

		// force download  
		header("Content-Type: application/force-download");
		header("Content-Type: application/octet-stream");
		header("Content-Type: application/download");

		// disposition / encoding on response body
		header("Content-Disposition: attachment;filename={$filename}");
		header("Content-Transfer-Encoding: binary");
	}

	private function array2csv(array &$datas) {
	   if (count($datas) == 0) {
		 return null;
	   }
	   ob_start();
	   $df = fopen("php://output", 'w');
       fwrite($df, chr(0xEF).chr(0xBB).chr(0xBF));
	   fputcsv($df, array_keys(reset($datas)));
	   foreach ($datas as $row) {
		  fputcsv($df, $row);
	   }
	   fclose($df);
	   return ob_get_clean();
	}

	private function renderReportData(&$objPHPExcel, $datas, $title) {
		$objPHPExcel->setActiveSheetIndex(0);
		// 填写表头和数据
		foreach ($datas as $row => $data) {
			$col = 2;
			foreach ($data as $key => $val) {
				// 添加 row head
				if ($row == 0) {
					$objPHPExcel->getActiveSheet()
								->setCellValueByColumnAndRow($col, $row+4, $key);
				}
				$objPHPExcel->getActiveSheet()
							->setCellValueByColumnAndRow($col, $row+5, $val);
								
				$col++;
			}			
		}
		// Rename worksheet
		$objPHPExcel->getActiveSheet()->setTitle($title);
	}

	//渲染原始数据
	private function renderRawData(&$objPHPExcel, $datas, $title, $rawSheetIndex) {
		// 填写表头和数据
		// row 从 1 开始索引，column 从 0 开始
		$objPHPExcel->setActiveSheetIndex($rawSheetIndex);
		foreach ($datas as $row => $data) {
			$col = 0;
			foreach ($data as $key => $val) {
				// 添加 row head
				if ($row == 0) {
					$objPHPExcel->getActiveSheet()
								->setCellValueByColumnAndRow($col, $row+1, $key);
				}
				$objPHPExcel->getActiveSheet()
							->setCellValueByColumnAndRow($col, $row+2, $val);
								
				$col++;
			}
		}
		$objPHPExcel->getDefaultStyle()->getFont()->setName('Times New Roman')->setSize(12);
		$objPHPExcel->getActiveSheet()->setShowGridlines(true);
		$objPHPExcel->getActiveSheet()->setTitle($title.'-原始数据');
		/*
		$objPHPExcel->getActiveSheet()
					->getDefaultStyle()
					->applyFromArray(['fill' => [
										'type'  => \PHPExcel_Style_Fill::FILL_NONE,
										'color' => ['argb' => '00777777']
									],
					]);
		*/
	}

	//渲染vamaker报表框架
	private function setReportBorder(&$objPHPExcel, $title, $isFreeze) {
		// 数据量超过 14 行冻结头
        if ($isFreeze) {
            $objPHPExcel->getActiveSheet()->freezePane('Z5');
        }
		// 头宽度自适应
		
		// slow and not work fine
		//\PHPExcel_Shared_Font::setAutoSizeMethod(\PHPExcel_Shared_Font::AUTOSIZE_METHOD_APPROX);
		
		$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();
		$highestColumn = $objPHPExcel->getActiveSheet()->getHighestColumn();
		for($col = 'C'; $col <= $highestColumn; $col++) {
			$objPHPExcel->getActiveSheet()
						->getColumnDimension($col)
						->setAutoSize(false);
		}
		 /*
		foreach($objPHPExcel->getActiveSheet()->getColumnDimension() as $col) {
			$col->setAutoSize(true);
		}

		for($i = $fromCol; $i !== $toCol; $i++) {
			$calculatedWidth = $sheet->getColumnDimension($i)->getWidth();
			$sheet->getColumnDimension($i)->setWidth((int) $calculatedWidth * 1.05);
		}
		 */
		$objPHPExcel->getActiveSheet()
					->getStyle("B4:".$highestColumn."4")
					->applyFromArray(["font" => ["bold" => true, 
												 "color" => ['argb' =>'ffffffff'],
												 'size' => '10',
									]
					]);
		$borderColor = '3A3A3A';

		$panColumn = \PHPExcel_Cell::columnIndexFromString($highestColumn);
		$panColumn = \PHPExcel_Cell::stringFromColumnIndex($panColumn);
		$panRow = $highestRow + 3;

		
		$objPHPExcel->getActiveSheet()
					->getStyle($panColumn.$panRow)
					->getAlignment()
					->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
					->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		//设置 vamaker 报表背景色
		/*
		$objPHPExcel->getActiveSheet()
					->getDefaultStyle()
					->applyFromArray(['fill' => [
										//'type'  => \PHPExcel_Style_Fill::FILL_SOLID,
										'color' => ['argb' => '00777777']
									],
					]);
					*/
		$objPHPExcel->getActiveSheet(0)->setShowGridlines(false);
		$objPHPExcel->getActiveSheet()
					->getStyle("C2:".$panColumn.$panRow)
					->getFill()
					->setFillType(\PHPExcel_Style_Fill::FILL_SOLID)
					->getStartColor()
					->setARGB('00ffffff');
		$objPHPExcel->getActiveSheet()
					->getColumnDimension('A')
					->setWidth(3);

		// border 样式
		// top
		$objPHPExcel->getActiveSheet()
					->getStyle("B2:".$panColumn."2")
					->getFill()
					->applyFromArray(['type'       => \PHPExcel_Style_Fill::FILL_SOLID,
									  'startcolor' => array('rgb' => $borderColor),
									]);
		$objPHPExcel->getActiveSheet()
					->getColumnDimension('B')
					->setWidth(1);
		// left
		$objPHPExcel->getActiveSheet()
					->getStyle("B2:B".$panRow)
					->getFill()
					->applyFromArray(['type'       => \PHPExcel_Style_Fill::FILL_SOLID,
									  'startcolor' => array('rgb' => $borderColor),
									]);
		$objPHPExcel->getActiveSheet()
					->getRowDimension('2')
					->setRowHeight(6);
		//bottom
		$objPHPExcel->getActiveSheet()
					->getStyle("B".$panRow.":".$panColumn.$panRow)
					->getFill()
					->applyFromArray(['type'       => \PHPExcel_Style_Fill::FILL_SOLID,
									  'startcolor' => array('rgb' => $borderColor),
									]);
		$objPHPExcel->getActiveSheet()
					->getRowDimension($panRow)
					->setRowHeight(6);
		//right
		$objPHPExcel->getActiveSheet()
					->getStyle($panColumn."2".":".$panColumn.$panRow)
					->getFill()
					->applyFromArray(['type'       => \PHPExcel_Style_Fill::FILL_SOLID,
									  'startcolor' => array('rgb' => $borderColor),
									]);
		$objPHPExcel->getActiveSheet()
					->getColumnDimension($panColumn)
					->setWidth(1);
		// 头样式
		$objPHPExcel->getActiveSheet()
					->getStyle("C4:".$highestColumn."4")
					->getFill()
					->applyFromArray(['type'       => \PHPExcel_Style_Fill::FILL_SOLID,
									  'startcolor' => array('rgb' => $borderColor),
									]);

		// 报表标题
		$objPHPExcel->getActiveSheet()
					->getRowDimension(3)
					->setRowHeight(48);
		$objPHPExcel->setActiveSheetIndex(0)->mergeCells('C3:'.$highestColumn.'3');
		$objPHPExcel->getActiveSheet()->setCellValue('C3', $title);
		$objPHPExcel->getActiveSheet()
					->getStyle("C3")
					->getFont()
                    ->setSize(24);
		$objPHPExcel->getActiveSheet()
					->getStyle('C3')
					->getAlignment()
					->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
					->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		// 报表头
		$objPHPExcel->getActiveSheet()
					->getStyle('C4:'.$highestColumn.$highestRow)
					->getAlignment()
					->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
					->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
		$objPHPExcel->getActiveSheet()->setAutoFilter('C4:'.$highestColumn.$highestRow);
		// 报表尾
		$objPHPExcel->getActiveSheet()
					->getRowDimension($panRow-2)
					->setRowHeight(24);
		$objPHPExcel->getActiveSheet()
					->getRowDimension($panRow-1)
					->setRowHeight(48);
		$copyRow = $panRow - 1;
		$objPHPExcel->setActiveSheetIndex(0)
					->mergeCells('C'.$copyRow.':'.$highestColumn.$copyRow);
		$objPHPExcel->getActiveSheet()
					->setCellValue('C'.$copyRow, "万流客 VaMaker\nSSP.VAMAKER.COM\nCopyright© 2014-2016 Vamaker All Rights Reserved");
		$objPHPExcel->getActiveSheet()->getStyle('C'.$copyRow)->getAlignment()->setWrapText(true);
		$objPHPExcel->getActiveSheet()
					->getStyle('C'.$copyRow)
					->getFont()
                    ->setSize(10);
		$objPHPExcel->getActiveSheet()
					->getStyle('C'.$copyRow)
					->getAlignment()
					->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
					->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

		// 报表内容 border
		$styleArray = [
			'borders' => [
				'allborders' => [
					'style' => \PHPExcel_Style_Border::BORDER_THIN
				]
			]
		];
		$outlineStyleArray = [
			'borders' => [
				'bottom' => [
					'style' => \PHPExcel_Style_Border::BORDER_THICK
				]
			]
		];
		
		$objPHPExcel->getActiveSheet()
					->getStyle('C4:'.$highestColumn.$highestRow)
					->applyFromArray($styleArray);
		$objPHPExcel->getActiveSheet()
					->getStyle('C4:'.$highestColumn.$highestRow)
					->applyFromArray($outlineStyleArray);
		//所有数据居右
		$objPHPExcel->getActiveSheet()
					->getStyle('C5:'.$highestColumn.$highestRow)
					->getAlignment()
					->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER)
					->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

		unset($styleArray);
		unset($outlineStyleArray);
	}

	private function render($filename, &$objPHPExcel) {
		// Redirect output to a client’s web browser (Excel5)
		header('Content-Type: application/vnd.ms-excel');
		header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
		header('Cache-Control: max-age=0');
		// If you're serving to IE 9, then the following may be needed
		header('Cache-Control: max-age=1');

		// If you're serving to IE over SSL, then the following may be needed
		header ('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
		header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT'); // always modified
		header ('Cache-Control: cache, must-revalidate'); // HTTP/1.1
		header ('Pragma: public'); // HTTP/1.0

		//$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
		$objWriter->save('php://output');
	}

	/**
	 * set vamaker logo
	 */
	private function setLogo(&$objPHPExcel) {
		//vendor('PHPExcel.Classes.PHPExcel');
		// icon
		$logoPath = getcwd().'/'.Yii::$app->params['envDir'].'/img/new-logo-dark.png';
		//echo $logoPath = Yii::$app->params['logo'];
		//echo $logoPath; exit;
		$gdImage = imagecreatefrompng($logoPath);
		imagealphablending($gdImage, false);
		list($width, $height) = getimagesize($logoPath);

		$thumb = imagecreatetruecolor($width, $height);
		imagealphablending($thumb, false);
		imagesavealpha($thumb, true);
		imagecopyresampled($thumb, $gdImage, 0, 0, 0, 0, $width, $height, $width, $height);

		$objDrawing = new \PHPExcel_Worksheet_MemoryDrawing();
		$objDrawing->setName('ValueMaker');
		$objDrawing->setDescription('ValueMaker');
		$objDrawing->setImageResource($thumb);
		$objDrawing->setRenderingFunction(\PHPExcel_Worksheet_MemoryDrawing::RENDERING_PNG);
		$objDrawing->setMimeType(\PHPExcel_Worksheet_MemoryDrawing::MIMETYPE_DEFAULT);
		$objDrawing->setHeight($height);
		//$objDrawing->setWidth($width-5);
		$objDrawing->setOffsetX(2);
		$objDrawing->setOffsetY(5);
		$objDrawing->setCoordinates('C3');
		$objDrawing->setWorksheet($objPHPExcel->getActiveSheet());
		unset($objDrawing);
	}

	public function readXlsx($inputFileName) {
		//vendor('PHPExcel.Classes.PHPExcel');
		//  Read your Excel workbook
		try {
			$inputFileType = \PHPExcel_IOFactory::identify($inputFileName);
			$objReader = \PHPExcel_IOFactory::createReader($inputFileType);
			$objPHPExcel = $objReader->load($inputFileName);
		} catch (Exception $e) {
			die('Error loading file "' . pathinfo($inputFileName, PATHINFO_BASENAME) 
			. '": ' . $e->getMessage());
		}

		//  Get worksheet dimensions
		$sheet = $objPHPExcel->getSheet(0);
		$highestRow = $sheet->getHighestRow();
		$highestColumn = $sheet->getHighestColumn();

		//  Loop through each row of the worksheet in turn
		$datas = [];
		$fieldName = [];

		for ($row = 1; $row <= $highestRow; $row++) {
			//  Read a row of data into an array
			$rowData = $sheet->rangeToArray('A'.$row.':'.$highestColumn.$row, NULL, TRUE, FALSE);
			if ($row == 1) {
				$fieldName = $rowData[0];
				continue;
			}
			$datas[] = array_combine($fieldName, $rowData[0]);
		}
		return $datas;
	}
}//end
